﻿using Aspose.Cells;
using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Web;
using System.Xml.Linq;

namespace ExcelImport.Entity
{
    public class ErrorExcelHelp
    {
        public Workbook errorWorkbook { get; set; }
        private Dictionary<string, int> excelHeader = new Dictionary<string, int>();
        private int rowIndex;

        #region Excel处理方法

        /// <summary>
        /// 初始化错误Workbook
        /// </summary>
        /// <param name="stream">导入数据流</param>
        /// <param name="columns">对应xml信息</param>
        public ErrorExcelHelp(Stream stream, IEnumerable<XElement> columns)
        {
            errorWorkbook = new Workbook(stream);
            errorWorkbook = ErrorExcelHelp.deleteUnnecessary(errorWorkbook, columns);
            //添加失败原因列
            int failReasonColIndex = errorWorkbook.Worksheets[0].Cells.MaxColumn + 1;
            errorWorkbook.Worksheets[0].Cells.InsertColumn(failReasonColIndex);
            Cell cell = errorWorkbook.Worksheets[0].Cells[0, failReasonColIndex];
            Style style = cell.GetStyle();
            style.BackgroundColor = Color.Red;
            style.Pattern = BackgroundType.Solid;

            style.Font.Name = "微软雅黑";
            cell.SetStyle(style);
            cell.PutValue("失败原因");

            //删除数据行
            errorWorkbook.Worksheets[0].Cells.DeleteRows(1, errorWorkbook.Worksheets[0].Cells.MaxRow + 1);
            GetColumIndex();//初始化表头
            rowIndex = 1;//初始化列标
        }

        /// <summary>
        /// 保存表头下标
        /// </summary>
        public void GetColumIndex()
        {
            for (int i = 0; i <= errorWorkbook.Worksheets[0].Cells.MaxColumn; i++)
            {
                var columName = errorWorkbook.Worksheets[0].Cells.GetCell(0, i);
                if (columName != null || columName.Value != null)
                {
                    excelHeader.Add(columName.Value.ToString(), i);
                }
            }
        }

        /// <summary>
        /// 为Workbook添加错误行
        /// </summary>
        /// <param name="columns">对应xml信息</param>
        /// <param name="dataRow">错误行</param>
        /// <param name="msg">当前行的错误原因</param>
        public void AddWorkbookRow(IEnumerable<XElement> columns, Row dataRow, string message)
        {
            int index = 0;
            foreach (var item in columns)
            {
                Cell currentCell = ((List<Cell>)HttpContext.Current.Items["TableHeader"])
                    .FirstOrDefault(m => m.DisplayStringValue == (string)item.Attribute("HeaderText"));
                if (currentCell != null)
                {
                    int columIndex;
                    bool flag = excelHeader.TryGetValue((string)item.Attribute("HeaderText"), out columIndex);
                    if (flag)
                    {
                        Cell temp = errorWorkbook.Worksheets[0].Cells[rowIndex, columIndex];
                        temp.PutValue(dataRow.GetCellOrNull(currentCell.Column)?.StringValue);
                    }
                    index++;
                }
            }
            Cell tempMsg = errorWorkbook.Worksheets[0].Cells[rowIndex, errorWorkbook.Worksheets[0].Cells.MaxColumn];
            tempMsg.PutValue(message.Substring(message.IndexOf("：") + 1, message.Length - message.IndexOf("：") - 1));
            rowIndex++;
        }

        /// <summary>
        /// 保存Excel
        /// </summary>
        /// <param name="Server">HttpServerUtilityBase实例</param>
        /// <param name="tableName">表格名称</param>
        /// <returns>错误Excel保持地址</returns>
        public string SaveWorkbook(HttpServerUtilityBase Server, string tableName)
        {
            string path = @"/" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + "-" + tableName + ".xlsx";
            errorWorkbook.Save(Server.MapPath(path), new OoxmlSaveOptions());
            return path;
        }

        #endregion

        #region 静态方法

        /// <summary>
        /// 清楚表格中无效列
        /// </summary>
        /// <param name="Workbook">实例对象</param>
        /// <param name="columns">xml信息</param>
        /// <returns></returns>
        public static Workbook deleteUnnecessary(Workbook Workbook, IEnumerable<XElement> columns)
        {
            List<string> xmlHeaderName = new List<string>();
            foreach (var item in columns)
            {
                xmlHeaderName.Add((string)item.Attribute("HeaderText"));
            }
            int maxColumn = Workbook.Worksheets[0].Cells.MaxColumn;

            //删除不需要的列
            for (int i = maxColumn; i >= 0; i--)
            {
                var columName = Workbook.Worksheets[0].Cells.GetCell(0, i);
                if (columName == null || columName.Value == null || columName.Value.ToString() == "失败原因" || !xmlHeaderName.Contains(columName.Value.ToString()))
                {
                    Workbook.Worksheets[0].Cells.DeleteColumn(i);
                }
            }
            return Workbook;
        }

        #endregion
    }
}